package com.san.app.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.san.app.domain.XyyzGrDO;
import com.san.app.domain.XyyzQyDO;
import com.san.base.dao.BaseManager;
import com.san.common.datasource.JdbcManager;
import com.san.common.utils.PageUtils;
import com.san.common.utils.Query;
import com.san.common.utils.StringUtils;
import com.san.metadata.domain.DataSourceDO;
import com.san.metadata.domain.ManageTableDO;
import com.san.metadata.service.DataSourceService;
import com.san.metadata.service.ManageMetadataService;

/**
 * 信用驿站JDBC访问层
 * 
 * @author xiangyz
 *
 */
@Service
public class XyyzManager extends BaseManager {

	/**
	 * 信用驿站企业表名
	 */
	private final static String XYYZ_TABLENAME_QY = "M_XYYZ_QY";

	/**
	 * 信用驿站个人表名
	 */
	private final static String XYYZ_TABLENAME_GR = "M_XYYZ_GR";

	/**
	 * 管理元数据服务接口
	 */
	@Autowired
	private ManageMetadataService manageMetadataService;

	/**
	 * 数据源服务接口
	 */
	@Autowired
	private DataSourceService dataSourceService;

	/**
	 * 根据表获取JDBC连接
	 * 
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public DruidPooledConnection getConn(String tableName) throws Exception {

		ManageTableDO manageTableDO = manageMetadataService.getByName(tableName);

		DruidPooledConnection conn = null;
		if (null != manageTableDO && null != manageTableDO.getTargetSourceId()) {
			DataSourceDO dataSourceDO = (DataSourceDO) dataSourceService.getById(manageTableDO.getTargetSourceId());

			if (null != dataSourceDO && !StringUtils.isEmpty(dataSourceDO.getSourceCode())) {
				conn = this.getDBConnection(dataSourceDO.getSourceCode());
			}

		}
		return conn;
	}

	/**
	 * 信用驿站企业信息
	 * 
	 * @param qymc
	 * @return
	 */
	public PageUtils queryQy(Query query) {
		List<XyyzQyDO> resultList = new ArrayList<XyyzQyDO>();
		int total = 0;

		String qymc = (String) query.get("qymc");
		if (StringUtils.isEmpty(qymc)) {
			return new PageUtils(resultList, total);
		}
		Integer offset = (Integer) query.get("offset");
		if (null == offset) {
			offset = 0;
		}
		Integer limit = (Integer) query.get("limit");
		if (null == limit) {
			offset = 10;
		}

		try {
			JdbcManager manager = new JdbcManager();

			String sql = "select f01 as qymc,f03 as lrsy,date_format(f04,'%y-%m-%d') as lrsj,"
					+ "f05 as fbdw from M_XYYZ_QY where zt != '-1' and instr(f01,?) > 0 limit ?, ?";

			List<Map<String, Object>> list = manager.executeQuery(this.getConn(XYYZ_TABLENAME_QY), sql,
					new Object[] { qymc, offset, limit });
			for (Map<String, Object> map : list) {
				XyyzQyDO qyDO = new XyyzQyDO();
				qyDO.setFbdw((String) map.get("fbdw"));
				qyDO.setLrsj((String) map.get("lrsj"));
				qyDO.setLrsy((String) map.get("lrsy"));
				qyDO.setQymc((String) map.get("qymc"));
				resultList.add(qyDO);
			}

			String countSql = "select count(*) ct from M_XYYZ_QY where zt != '-1' and instr(f01,?) > 0";
			List<Map<String, Object>> countList = manager.executeQuery(this.getConn(XYYZ_TABLENAME_QY), countSql,
					new Object[] { qymc });
			if (null != countList) {
				Map<String, Object> map = countList.get(0);
				total = ((Long) map.get("ct")).intValue();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return new PageUtils(resultList, total);
	}

	/**
	 * 信用驿站个人信息
	 * 
	 * @param qymc
	 * @return
	 */
	public PageUtils queryGr(Query query) {
		List<XyyzGrDO> resultList = new ArrayList<XyyzGrDO>();
		int total = 0;

		String xm = (String) query.get("xm");
		if (StringUtils.isEmpty(xm)) {
			return new PageUtils(resultList, total);
		}
		Integer offset = (Integer) query.get("offset");
		if (null == offset) {
			offset = 0;
		}
		Integer limit = (Integer) query.get("limit");
		if (null == limit) {
			offset = 10;
		}

		try {
			DruidPooledConnection conn = this.getConn(XYYZ_TABLENAME_GR);

			JdbcManager manager = new JdbcManager();

			String sql = "select f01 as xm,CONCAT(LEFT(f02,3), '************' ,RIGHT(f02,4)) as sfzhm,"
					+ "f03 as lrsy,date_format(f04,'%y-%m-%d') as lrsj,f05 as fbdw from M_XYYZ_GR "
					+ "where zt != '-1' and instr(f01,?) > 0 limit ?, ?";

			List<Map<String, Object>> list = manager.executeQuery(conn, sql, new Object[] { xm, offset, limit });
			for (Map<String, Object> map : list) {
				XyyzGrDO grDO = new XyyzGrDO();
				grDO.setFbdw((String) map.get("fbdw"));
				grDO.setLrsj((String) map.get("lrsj"));
				grDO.setLrsy((String) map.get("lrsy"));
				grDO.setXm((String) map.get("xm"));
				grDO.setSfzhm((String) map.get("sfzhm"));
				resultList.add(grDO);
			}

			String countSql = "select count(*) ct from M_XYYZ_GR where zt != '-1' and instr(f01,?) > 0";
			List<Map<String, Object>> countList = manager.executeQuery(this.getConn(XYYZ_TABLENAME_QY), countSql,
					new Object[] { xm });
			if (null != countList) {
				Map<String, Object> map = countList.get(0);
				total = ((Long) map.get("ct")).intValue();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return new PageUtils(resultList, total);
	}
}
